package xu.jiang.report.dealer.report;

import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import xu.jiang.report.Param;
import xu.jiang.report.bean.*;
import xu.jiang.report.dealer.ISQLRunner;
import xu.jiang.report.dealer.sheet.SheetDealer;
import xu.jiang.report.dto.LayerDTO;
import xu.jiang.report.poi.DomPOI;
import xu.jiang.report.util.PathUtil;
import xu.jiang.report.util.file.FileUtil;
import xu.jiang.report.util.report.POIUtil;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Path;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 报表处理总入口
 * 
 * @see
 * @author jx
 * @date 2024-10-27
 */
public abstract class AbstractReportDealer{

	/**
	 * 这个贯穿全剧的公共参数 存储了每个模块都会使用的参数
	 */
	private final Param param;


//	private  ISQLRunner sqlRunner;

	private Workbook workbook;
	private File outputFile;

	/**
	 * 报表输出路径
	 */
	private Path outDir = null;

	public AbstractReportDealer(Param param)  {
		this.param = param;

	}

	protected  abstract ISQLRunner getSqlRunner();

	/**
	 * 开始执行
	 * 
	 * @throws Exception
	 * @throws Exception
	 */
	public File start() throws Exception {
		try {

			this.before();

			// 读取Excel模板
			this.workbook = DomPOI.readExcel(this.param.templet);

			// 默认获取第一个sheet作为模板sheet,并保存到公共变量param里
			this.param.mainSheet = workbook.getSheetAt(0);

			/**
			 * 重绘模板，将模板固化
			 */
			Painter painter = new Painter(this.param.uiParams, this.param.mainSheet).draw();


			/*
			 * 解析模板参数
			 */
			new ParamAnalyzer(this.param);

			/*
			 * 分页处理，一个sheet算作一页
			 */
			PagingAnalyzer pagingAnalyzer = new PagingAnalyzer(this.param);

			List<SheetDealer> sheets = pagingAnalyzer.page2Sheet(this.getSqlRunner(),painter.sheetname_modelSheet);
			// 循环处理每个sheet
			for (SheetDealer sheetDealer : sheets) {
				// 先清理上一页的缓存，在处理下一页
				sheetDealer.clearCache().runing(this.getSqlRunner());
			}
			// 删除主模板....
			this.workbook.removeSheetAt(0);
			/*
			 * 如果没有模板了，要留个空白页
			 */
			if (sheets.isEmpty()) {
				this.workbook.createSheet("no data");
			}
			System.out.println("恭喜您!!");

		} catch (Throwable e) {
			// TODO Auto-generated catch block
//			String msg = DataHelper.filterValueBy(e.getMessage());
			String msg = e.getMessage();
			// 用空串替换所有的替换符，
			// 并将错误信息显示在文件的第一个空白单元格中
			SheetDealer.BlankSheet.makeAllCellsBlank(msg,
					POIUtil.renameSheet(this.workbook.cloneSheet(0), SheetDealer.DEFAULT_SHEET_NAME));
			
			e.printStackTrace();
		} finally {
			this.finishInServer();
		}
		return this.outputFile;

	}

	/**
	 * 生成报表之前要做的事情
	 * 如果有需要，你可以重新该函数
	 * @return
	 */
	public AbstractReportDealer before() {
		
		return this ;
	}



	public static void main(String[] args) {

		String jsonStr = "{\"index\":5,\"columns\":[{\"key\":\"序号\",\"value\":\"{sys.rowno}\"},{\"key\":\"施工内容\",\"value\":\"{w.projectUnitName}\"},{\"key\":\"单元号\",\"value\":\"{w.projectUnitCode}\"},{\"key\":\"单位\",\"value\":\"当量\"},{\"key\":\"总量\",\"value\":\"sum({w.theoreticalDiameter})\"},{\"key\":\"今日完成量\",\"value\":\"sum({w.theoreticalDiameter})< {w.weldingDate}>= ' 2024/11/23 'and {w.weldingDate}<='2024/11/24' >\"},{\"key\":\"累计完成量\",\"value\":\"sum({w.theoreticalDiameter})< {w.weldingDate} is not null >\"},{\"key\":\"累计完成比例\",\"value\":\"CONCAT(round(((ifnull(sum({w.theoreticalDiameter})<{w.weldingDate} is not null >,0))/sum({w.theoreticalDiameter}))*100,2),'%')\"},{\"key\":\"施工人数(电焊)\",\"value\":\"count(distinct {w.welderId})< {w.weldingDate}>= ' 2024/11/23 'and {w.weldingDate}<='2024/11/24' >\"},{\"key\":\"备注\",\"value\":\"\"},{\"key\":\"\",\"value\":\" whereBy( {w.weldNumber} not like '%R%' )\"},{\"key\":\"\",\"value\":\" orderBy({w.projectItemCode}+,{w.projectUnitCode}+)\"},{\"key\":\"\",\"value\":\"groupBy({w.projectItemId},{w.projectUnitId})\"}]}";

		// init params
		Map<String, Class<StringKeyValue>> classMap = new HashMap<>();
		classMap.put("columns", StringKeyValue.class);

		JSONObject json = JSONObject.fromObject(jsonStr);
		LayerDTO layerDto = (LayerDTO) JSONObject.toBean(json, LayerDTO.class, classMap);
		System.out.println(layerDto);
	}


	/**
	 * 在服务器上收尾，将修改后的Workbook输出到新的文件
	 * 
	 * @throws IOException
	 */
	private void finishInServer() throws IOException {
		this.outDir = (this.outDir == null ? PathUtil.getTempSavePath(null) : this.outDir);
		if(!this.outDir.toFile().exists()) {
			this.outDir.toFile().mkdirs();
		}
		Path path = this.outDir
				.resolve(UUID.randomUUID().toString() + FileUtil.getSuffix(this.param.templet.getName(), true));
		this.outputFile = path.toFile();
		FileOutputStream out = new FileOutputStream(this.outputFile);
		workbook.write(out);
		out.close();

	}

	/**
	 * 指定输出路径
	 * 
	 * @param path
	 */
	public AbstractReportDealer setOutDir(Path path) {
		if (path == null) {
			return this;
		}
		this.outDir = path;
		return this;
	}

	

	/**
	 * 获取order By的sql属性
	 * 
	 * @param v
	 *            :形如："orderBy({wl.jiang}+,{wl.xuxu}-)"
	 * @return 形如：List<String> [{wl.jiang}+, {wl.xuxu}-]
	 */
	public static List<String> getColumnsRegex4OrderBySql(String v) {
		List<String> olist = new ArrayList<String>();

		/*
		 * //该方法只能获取2个以内的排序字段，多了就只获取收尾2个 Pattern p =
		 * Pattern.compile(R.Layer.OrderByRegex); Matcher m = p.matcher(v); if
		 * (m.find()) { for (int i = 0; i < m.groupCount(); i++) { //
		 * System.out.println(m.group(i)); String tem = m.group(i); if
		 * (tem.matches(R.Layer.OrderByRegex_piece)) { System.out.println(tem);
		 * olist.add(tem); }
		 * 
		 * } }
		 */
		v = v.substring("orderBy(".length(), v.length() - 1);
		String[] arr = v.split(",");
		for (String a : arr) {
			if (StringUtils.isBlank(a)) {
				continue;
			}
			a = a.trim();
			if (a.endsWith("+") || a.endsWith("-")) {
				a = a.replace("+", " asc").replace("-", " desc ");
			} else {
				a += " asc ";
			}
			olist.add(a);
		}
		return olist;
	}

	/**
	 * 克隆sheet
	 * 
	 * @param sheet
	 * @return
	 */
	public static Sheet cloneSheet(Sheet sheet) {
		if (sheet == null) {
			return null;
		}
		Workbook wk = sheet.getWorkbook();
		// 获取索引
		int index_modelSheet = wk.getSheetIndex(sheet.getSheetName());

		return wk.cloneSheet(index_modelSheet);
	}

	/**
	 * 获取开始日期和结束日期之间的日期范围
	 * 
	 * @param start
	 * @param end
	 * @return List<String> 永不为null
	 * @throws ParseException
	 */
	public static List<String> getRangeDate(String start, String end) {
		// String start = "2018/02/19";
		// String end = "2018/03/02";
		List<String> arrDate = new ArrayList<String>();
		if (StringUtils.isBlank(start) || StringUtils.isBlank(end)) {
			return arrDate;
		}

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
		// 循环日期
		Calendar ca = Calendar.getInstance();
		Date curDate = null;
		Date endDate = null;
		try {
			curDate = sdf.parse(start);
			endDate = sdf.parse(end);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		}

		if (curDate == null || endDate == null) {

			return arrDate;
		}

		while (curDate.compareTo(endDate) <= 0) {
			// 放入值集合
			arrDate.add(sdf.format(curDate));

			ca.setTime(curDate);
			// 业务处理...
			ca.add(Calendar.DATE, 1);
			curDate = ca.getTime();
		}
		return arrDate;
	}

}
